package com.project.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * author: WheelChen
 * data: 2017/7/21
 * function: 导入excel信息
 */
public class ExcelUtil {

    /**
     * 解析
     * 第一行是标题行
     * 第二行以后都是内容
     * 例如：
     * id sex name
     * 1   男     王
     * 2   女     李
     * <p>
     * <p>
     * 解析后的map格式：
     * key  value
     * 0     List()一行
     * 1     List()一行
     * 2     List()一行
     * <p>
     * 例如：
     * 0    [id ,  name, sex,   sal   ,  date]
     * 1    [1.0, wang, 1.0, 1000.0, 42287.0]
     * 2    [2.0, liang, 1.0, 1001.0, 42288.0]
     *
     * @param in 输入流
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Map parse(InputStream in) throws IOException, InvalidFormatException {
        //提取并创建工作表
        Workbook wb = createWorkbook(in);
        //获取sheet页第0页
        Sheet sheet = wb.getSheetAt(0);
        //获取行迭代器
        Iterator rows = sheet.rowIterator();
        //解析出来的数据存放到这个map里面，value套了一层List，key用于存放标题，List用于存放标题下的所有数据
        Map<String, List<String>> map = new HashMap<String, List<String>>();

        String title = null;//标题
        int rowindex = 0;//行数
        int cellindex = 0;//列数
        String data = null;//用于暂存数据
        while (rows.hasNext()) {
            List<String> list = new ArrayList<String>();
            cellindex = 0;
            //获取行中数据
            Row row = (Row) rows.next();
            //获取列迭代器
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                //获取列中数据
                Cell cell = (Cell) cells.next();
                //获取每个单元格的值
                //将标题下的内容放到list中
                    list.add(getValue4Cell(cell));
            }
            //将解析完的一列数据压入map
            map.put("" + rowindex++, list);
        }
        in.close();
        return map;
    }

    /**
     * 提取单元格中的值
     */
    private static String getValue4Cell(Cell cell) {
        String data = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字 (防止以科学技术法)
                cell.setCellType(Cell.CELL_TYPE_STRING);
                data = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                data = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                data = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                data = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                data = String.valueOf("");
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                System.out.println(" 故障");
                break;
            default:
                System.out.print("未知类型  ");
                break;
        }
        return data;
    }

    /**
     * 提取文件并创建工作表
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    private static Workbook createWorkbook(InputStream in) throws InvalidFormatException, IOException {
        //如果创建工作表失败会抛出IO异常
        Workbook wb = WorkbookFactory.create(in);
        return wb;
    }

    /**
     * @author WheelChen
     * date: 2017/8/11
     * function: 初始化excel创建第一张表单的名称
     *
     * @param wb excel表
     * @param sheetName 单个sheet的名称
     * @return excel表
     */
    public static void createSheet(Workbook wb, String sheetName){
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);
    }
    /**
     * author: WheelChen
     * date: 2017/8/11
     * function: 创建标题
     *
     * @param sheet
     * @param Titles
     * @return
     */
    public static void createTitle(Sheet sheet, List<String> Titles){
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short)0);
        // Create a cell and put a value in it.
        for (int i = 0; i < Titles.size(); i++) {
            row.createCell(i).setCellValue(Titles.get(i));
        }
    }

    /**
     *  author: WheelChen
     * date: 2017/8/13
     * function:
     *
     * @param file
     * @param errorData
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void importData(File file, Map<String, List<String>> errorData) throws IOException, InvalidFormatException {
        Workbook wb = new XSSFWorkbook();
        createSheet(wb, "导入失败数据");
        Sheet sheet = wb.getSheetAt(0);
        createTitle(sheet, errorData.get("title"));
        errorData.remove("title");
        short index = 1;
        for (Map.Entry<String, List<String>> e : errorData.entrySet()) {
            List<String> data = e.getValue();
            if (data != null) {
                Row row = sheet.createRow(index);
                for (int i = 0; i < data.size(); i++) {
                    row.createCell(i).setCellValue(data.get(i));
                }
                index++;
            }
        }
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        System.out.println("写入excle成功");
    }

}
